package com.eorchis.module.trainingquestionnaire.dao.impl;

import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.eorchis.core.basedao.dao.impl.HibernateAbstractBaseDao;
import com.eorchis.core.basedao.entity.IBaseEntity;
import com.eorchis.core.basedao.query.condition.IPagingQueryCondition;
import com.eorchis.core.basedao.query.condition.builder.CompareType;
import com.eorchis.core.basedao.query.condition.builder.IConditionBuilder;
import com.eorchis.core.basedao.query.condition.builder.impl.DefaultQueryConditionBuilder;
import com.eorchis.core.page.commond.SortInfoBean;
import com.eorchis.core.ui.commond.IQueryCommond;
import com.eorchis.module.trainingneeds.domain.QueryQuestionBankBean;
import com.eorchis.module.trainingneeds.domain.QueryQuestionnaireBean;
import com.eorchis.module.trainingquestionnaire.dao.IQuestionnaireDao;
import com.eorchis.module.trainingquestionnaire.domain.ClassQuestionnaireSummary;
import com.eorchis.module.trainingquestionnaire.domain.QuestionnaireEntity;
import com.eorchis.module.trainingquestionnaire.domain.QuestionnaireQueryBean;
import com.eorchis.module.trainingquestionnaire.ui.commond.QuestionnaireQueryCommond;
import com.eorchis.module.trainingquestionnaire.ui.commond.QuestionnaireValidCommond;
import com.eorchis.module.trainneedscope.domain.TrainNeedScope;
import com.eorchis.util.SqlQueryUtil;
import com.eorchis.utils.utils.PropertyUtil;

/**
 * Title: QuestionnaireDaoImpl<br>
 * Description: 调查问卷数据接口实现<br>
 * Company: GoldGov<br>
 * Copyright @ 2012 GoldGov .All rights reserved.<br>
 * 
 * @author Administrator
 * @createDate 2014-08-27
 * @version $Revision$
 */
@SuppressWarnings("unchecked")
@Repository(value = "main.java.com.eorchis.module.trainingquestionnaire.dao.impl.QuestionnaireDaoImpl")
public class QuestionnaireDaoImpl extends HibernateAbstractBaseDao implements IQuestionnaireDao {

	public Class<? extends IBaseEntity> entityClass() {
		return QuestionnaireEntity.class;
	}

	public void queryConditionProcessor(IConditionBuilder defaultQueryConditionBuilder, IQueryCommond queryCommond) {
		QuestionnaireQueryCommond questionnaireQueryCommond = (QuestionnaireQueryCommond) queryCommond;
		defaultQueryConditionBuilder.setBaseQueryString("SELECT t FROM QuestionnaireEntity t");
		defaultQueryConditionBuilder.addCondition("t.questionnaireId", CompareType.IN, questionnaireQueryCommond.getSearchQuestionnaireIds());
		defaultQueryConditionBuilder.addCondition("t.questionnaireId", CompareType.EQUAL, questionnaireQueryCommond.getSearchQuestionnaireId());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.HQL);
	}

	/**
	 * 更新问卷活动状态
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Xupp
	 * @createDate 2014-8-28
	 */
	@Override
	public int updateQuestionnaireActiveState(QuestionnaireQueryCommond commond) throws Exception {
		StringBuffer hql = new StringBuffer();
		hql.append(" update QuestionnaireEntity t set t.activeState = :activeState where t.questionnaireId in (:id)");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", commond.getSearchQuestionnaireIds());
		params.put("activeState", commond.getActiveState());
		return this.executeUpdate(QueryStringType.HQL, hql.toString(), params);
	}

	/**
	 * 查询活动问卷列表
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Xupp
	 * @createDate 2014-8-27
	 */
	@Override
	public List<QuestionnaireQueryBean> queryListByActiveState(QuestionnaireQueryCommond commond) throws Exception {
		StringBuffer hql = new StringBuffer();
		Map<String, Object> params = new HashMap<String, Object>();
		hql.append(" select q.questionnaireId as questionnaireId ,q.questionnaireName as questionnaireName" + ",q.surveyObjectType as surveyObjectType,q.surveyType as surveyType"
				+ ",q.questionnaireExplain as questionnaireExplain,q.publishState as publishState,q.activeState as activeState"
				+ ",q.createDate as createDate from QuestionnaireEntity q where q.activeState = :activeState");
		// 问卷名称
		if (commond.getSearchQuestionnaireName() != null && !"".equals(commond.getSearchQuestionnaireName())) {
			hql.append(" and q.questionnaireName like :questionnaireName ");
			params.put("questionnaireName", "%" + commond.getSearchQuestionnaireName() + "%");
		}
		// 创建单位
		if (commond.getSearchCreateUnit() != null && !"".equals(commond.getSearchCreateUnit())) {
			hql.append(" and q.department.deptName like :createUnit");
			params.put("createUnit", "%" + commond.getSearchCreateUnit() + "%");
		}
		// 结束时间
		if (commond.getSearchEndTime() != null && !"".equals(commond.getSearchEndTime())) {
			hql.append(" and q.endDate <= :endDate  ");
			params.put("endDate", commond.getSearchEndTime());
		}
		// 开始时间
		if (commond.getSearchStartTime() != null && !"".equals(commond.getSearchStartTime())) {
			hql.append(" and q.beginDate >= :beginDate ");
			params.put("beginDate", commond.getSearchStartTime());
		}
		// 创建时间
		if (commond.getSearchCreateDate() != null && !"".equals(commond.getSearchCreateDate())) {
			hql.append(" and to_char(q.createDate,'yyyy-mm-dd') = :createDate ");
			params.put("createDate", new SimpleDateFormat("yyyy-MM-dd").format(commond.getSearchCreateDate()));
		}
		// 创建人
		if (commond.getSearchUserName() != null && !"".equals(commond.getSearchUserName())) {
			hql.append(" and q.user.userName like :userName ");
			params.put("userName", "%" + commond.getSearchUserName() + "%");
		}
		// 问卷类型
		if (commond.getSearchQuestionnaireType() != null && !"".equals(commond.getSearchQuestionnaireType())) {
			hql.append(" and q.questionnaireType.dataCode = :questionnaireType ");
			params.put("questionnaireType", commond.getSearchQuestionnaireType());
		}
		// 发布状态
		if (commond.getSearchPublishState() != null && !"".equals(commond.getSearchPublishState())) {
			hql.append(" and q.publishState = :publishState ");
			params.put("publishState", commond.getSearchPublishState());
		}
		if (commond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = commond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				hql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					hql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		} else {
			hql.append(" order by q.createDate desc ");
		}
		params.put("activeState", QuestionnaireQueryCommond.IS_ENABLE);

		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(QuestionnaireQueryBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);

		// 查询总数量
		Long count = this.count(queryCondition);
		int firstResult = commond.calculate(count.longValue());
		List<QuestionnaireQueryBean> findList = new ArrayList<QuestionnaireQueryBean>();
		findList = (List<QuestionnaireQueryBean>) this.findList(queryCondition, firstResult, commond.getLimit());
		return findList;
	}

	// /**
	// * 查询活动问卷
	// * @param queryCommond
	// * @return
	// * @throws Exception
	// * @author Xupp
	// * @createDate 2014-8-27
	// */
	// public QuestionnaireQueryBean
	// queryQuestionnaireInfo(QuestionnaireValidCommond commond)throws
	// Exception{
	// StringBuffer hql= new StringBuffer();
	// hql.append(" from QuestionnaireEntity  q   left join  q.user  left join  q.department  where  q.questionnaireId = :questionnaireId ");
	// Map<String,Object> params =new HashMap<String,Object>();
	// params.put("questionnaireId", commond.getQuestionnaireId());
	// List<QuestionnaireQueryBean> list = (List<QuestionnaireQueryBean>)
	// this.executeFind(QueryStringType.HQL, hql.toString(), params);
	// return list.get(0);
	// }
	/**
	 * 判断是否可以发布培训需求调查问卷
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-7
	 */
	@Override
	public boolean isPublish(QuestionnaireQueryCommond queryCommond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		String[] questionnaireIds = queryCommond.getSearchQuestionnaireIds();
		for (String id : questionnaireIds) {
			params.put("questionnaireId", id);
			String[] tables = { "questionnaire_question" };// ,"questionnaire_survey_object"

			for (String table : tables) {
				String sql = "select count(*) from " + table + " t where t.QUESTIONNAIRE_ID = :questionnaireId";
				List<BigInteger> count = (List<BigInteger>) this.executeFind(QueryStringType.SQL, sql, params);
				if (count.get(0).intValue()==0) {
					return false;
				}
			}
		}
		return true;
	}

	/**
	 * 发布撤销培训需求调查问卷
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-7
	 */
	@Override
	public int publishQuestionnaire(QuestionnaireQueryCommond queryCommond) throws Exception {
		StringBuffer sql = new StringBuffer("");
		Map<String, Object> params = new HashMap<String, Object>();
		sql.append("update questionnaire q set q.PUBLISH_STATE = :publishState where q.QUESTIONNAIRE_ID in (:questionnaireIds)");
		params.put("publishState", queryCommond.getPublishState());
		params.put("questionnaireIds", queryCommond.getSearchQuestionnaireIds());

		return this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
	}

	/**
	 * 预览问卷
	 * 
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-5-28
	 */
	@Override
	public List<QueryQuestionBankBean> queryQuestionnaireQuestion(QuestionnaireValidCommond commond) throws Exception {
		StringBuffer sql = new StringBuffer("");
		sql.append("select qb.QUESTION_ID as \"questionId\",qb.QUESTION_NAME as \"questionName\",qq.ORDER_NUM as \"orderNum\" ,"
				+ " qbo.OPTION_NUM as \"optionNum\",qbo.OPTION_NAME as \"optionName\", qb.is_score_type as \"isScoreType\","
				+ " qb.QUESTION_TYPE as \"questionType\",qq.IS_MUST as \"isMust\",qb.WORD_NUM as \"wordNum\" "
				+ " ,qb.QUESTIONS_PROBLEM_SETS as \"questionsProblemSets\",qbo.OBJECTIVE_TOPIC  \"objectiveTopic\"   ");
		sql.append(" from questionnaire_question qq " + " INNER JOIN question_bank qb ON qq.QUESTION_ID=qb.QUESTION_ID" + " LEFT JOIN question_bank_option qbo ON qq.QUESTION_ID=qbo.QUESTION_ID"
				+ " LEFT JOIN base_basedata bb ON qb.QUESTION_TYPE=bb.DATA_CODE" + " WHERE qq.QUESTIONNAIRE_ID = :questionnaireId");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("questionnaireId", commond.getQuestionnaireId());

		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setResultObject(QueryQuestionBankBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);

		List<QueryQuestionBankBean> resultList = (List<QueryQuestionBankBean>) this.findList(queryCondition);

		return resultList;
	}

	/**
	 * 问卷查看列表,查看已结束问卷调查情况
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-4-29
	 */
	@Override
	public List<QueryQuestionnaireBean> findEndQuestionnaireList(QuestionnaireQueryCommond queryCommond) throws Exception {
		StringBuffer sql = new StringBuffer("");
		Map<String, Object> params = new HashMap<String, Object>();
		sql.append("select * from (");
		sql.append("SELECT q.QUESTIONNAIRE_ID as questionnaireId," + " q.QUESTIONNAIRE_NAME as questionnaireName,bb.DATA_NAME as questionnaireType,q.BEGIN_DATE as beginDate,"
				+ " q.END_DATE as endDate,bu.user_name as userName,bd.DEPNAME as depName," + " (" + " select count(distinct qua.USERID) from questionnaire_user_answer qua"
				+ " inner join base_user bu on qua.USERID=bu.USERID" + " where qua.QUESTIONNAIRE_ID=q.QUESTIONNAIRE_ID" + " ) as surveyUserNumBig" + " FROM questionnaire q "
				+ " left join base_user bu on q.USERID=bu.USERID" + " left join base_department bd on q.DEPID=bd.DEPID" + " left join base_basedata bb on q.QUESTIONNAIRE_TYPE=bb.DATA_CODE where 1=1");
		// sql.append(" and q.END_DATE<"+"'"+DateUtil.getDateString(new Date(),
		// "yyyy-MM-dd")+"'");//只查询已结束的问卷
		if (PropertyUtil.objectNotEmpty(queryCommond.getActiveState())) {
			sql.append(" and q.ACTIVE_STATE = :activeState");
			params.put("activeState", queryCommond.getActiveState());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchQuestionnaireName())) {
			sql.append(" and q.QUESTIONNAIRE_NAME like :questionnaireName");
			params.put("questionnaireName", "%" + queryCommond.getSearchQuestionnaireName().trim() + "%");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchQuestionnaireType())) {
			sql.append(" and q.QUESTIONNAIRE_TYPE = :questionnaireType");
			params.put("questionnaireType", queryCommond.getSearchQuestionnaireType());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchStartTime())) {
			sql.append(" and q.BEGIN_DATE >= :beginDate");
			params.put("beginDate", queryCommond.getSearchStartTime());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchEndTime())) {
			sql.append(" and q.END_DATE <= :endDate");
			params.put("endDate", queryCommond.getSearchEndTime());
		}

		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			String userName = SqlQueryUtil.changeString(queryCommond.getSearchUserName().replace(" ", ""));
			sql.append(" and bu.user_name like :userName");
			params.put("userName", userName);
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchCreateUnit())) {
			sql.append(" and bd.DEPNAME like :depName");
			params.put("depName", "%" + queryCommond.getSearchCreateUnit().trim() + "%");
		}
		sql.append(") as t");
		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				sql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					sql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setResultObject(QueryQuestionnaireBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		Long count = this.count(queryCondition);
		List<QueryQuestionnaireBean> questionnaireBeanList = new ArrayList<QueryQuestionnaireBean>();
		if (queryCommond.getPage() == -1) {
			questionnaireBeanList = (List<QueryQuestionnaireBean>) this.findList(queryCondition);
		} else {
			int firstResult = queryCommond.calculate(count.longValue());
			questionnaireBeanList = (List<QueryQuestionnaireBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
		return questionnaireBeanList;
	}

	/**
	 * 统计分析，查询问卷计划调查人数
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-09-02
	 */
	@Override
	public List<String> querySurveyObjectNum(QuestionnaireQueryCommond queryCommond) throws Exception {
		StringBuffer sql = new StringBuffer("");
		sql.append("select concat(bd.TREEPATH,'%'),qso.DUTY_CODES" + " from questionnaire_survey_object qso" + " left join base_department bd on bd.DEPID=qso.SURVEY_OBJECT_ID"
				+ " where qso.QUESTIONNAIRE_ID = :questionnaireId and qso.SURVEY_OBJECT_TYPE = :surveyObjectType");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("questionnaireId", queryCommond.getSearchQuestionnaireId());
		params.put("surveyObjectType", TrainNeedScope.SURVER_TYPE_DEPT);

		List<Object[]> list = (List<Object[]>) this.executeFind(QueryStringType.SQL, sql.toString(), params);
		Map<String, String[]> treePathDutyCodeMap = new HashMap<String, String[]>();
		for (Object[] obj : list) {
			String[] dutyCodes = null;
			if (PropertyUtil.objectNotEmpty(obj[1])) {
				dutyCodes = obj[1].toString().split(",");
			}
			treePathDutyCodeMap.put(obj[0].toString(), dutyCodes);
		}
		sql.setLength(0);
		sql.append("select distinct t.userId from (");
		sql.append("select bdu.USERID as userId from base_department_user bdu" + " left join base_department bd on bdu.DEPID=bd.DEPID" + " left join base_user bu on bdu.USERID=bu.USERID"
				+ " where 1<>1");

		for (Map.Entry<String, String[]> map : treePathDutyCodeMap.entrySet()) {
			sql.append(" or (bd.TREEPATH like '" + map.getKey() + "'");
			if (PropertyUtil.objectNotEmpty(map.getValue())) {
				String codes = "";
				for (String str : map.getValue()) {
					codes += "'" + str + "',";
				}
				codes = codes.substring(0, codes.length() - 1);
				sql.append(" and bu.DUTY_LEVEL_CODE in (" + codes + ")");
			}
			sql.append(")");
		}
		sql.append(" union ");
		sql.append(" select qso.SURVEY_OBJECT_ID as userId from questionnaire_survey_object qso" + " inner join base_user bu on qso.SURVEY_OBJECT_ID=bu.USERID"
				+ " where qso.QUESTIONNAIRE_ID = :questionnaireId and qso.SURVEY_OBJECT_TYPE = :surveyObjectType");
		params.put("surveyObjectType", TrainNeedScope.SURVER_TYPE_USER);
		sql.append(" )  t");

		List<String> userIdList = (List<String>) this.executeFind(QueryStringType.SQL, sql.toString(), params);

		return userIdList;
	}

	/**
	 * 门户提醒信息，查询需要推送提醒信息的问卷
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-09-11
	 */
	@SuppressWarnings("deprecation")
	@Override
	public String[] queryQuestionnaireNeedSendInfo() throws Exception {
		StringBuffer sql = new StringBuffer("");
		sql.append("select q.QUESTIONNAIRE_ID from questionnaire q where q.PUBLISH_STATE = :publishState"
				+ " and q.ACTIVE_STATE = :activeState and q.BEGIN_DATE >= :beginDate and q.BEGIN_DATE <= :endDate");
		Map<String, Object> params = new HashMap<String, Object>();
		Date beginDate = new Date();
		beginDate.setHours(0);
		beginDate.setMinutes(0);
		beginDate.setSeconds(0);
		Date endDate = new Date();
		endDate.setHours(23);
		endDate.setMinutes(59);
		endDate.setSeconds(59);
		params.put("beginDate", beginDate);
		params.put("endDate", endDate);
		params.put("activeState", QuestionnaireQueryCommond.IS_ENABLE);
		params.put("publishState", QuestionnaireEntity.IS_PUBLISH_Y);

		List<String> questionnaireIdList = (List<String>) this.executeFind(QueryStringType.SQL, sql.toString(), params);

		String[] questionnaireIds = new String[questionnaireIdList.size()];
		if (questionnaireIdList.size() > 0) {
			questionnaireIds = questionnaireIdList.toArray(questionnaireIds);
		}
		return questionnaireIds;
	}

	/**
	 * 门户提醒信息，查询问卷调查范围
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-09-04
	 */
	@Override
	public List<TrainNeedScope> queryQuestionnaireSurveyScope(QuestionnaireQueryCommond queryCommond) throws Exception {
		String hql = "from TrainNeedScope t where t.questionnaire.questionnaireId in (:questionnaireId)";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("questionnaireId", queryCommond.getSearchQuestionnaireIds());

		List<TrainNeedScope> trainNeedScopeList = (List<TrainNeedScope>) this.executeFind(QueryStringType.HQL, hql, params);

		return trainNeedScopeList;
	}

	/**
	 * 判断用户是否已经参加调查问卷
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-09-10
	 */
	@Override
	public boolean isJoinQuestionnaire(QuestionnaireQueryCommond queryCommond) throws Exception {
		String sql = "select count(*) from questionnaire_user_answer qua where qua.USERID = :userId and qua.QUESTIONNAIRE_ID = :questionnaireId and qua.class_id =:classId ";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("questionnaireId", queryCommond.getSearchQuestionnaireId());
		params.put("userId", queryCommond.getSearchUserId());
		params.put("classId", queryCommond.getSearchClassId());
		List<Object> userAnswerCount = (List<Object>) this.executeFind(QueryStringType.SQL, sql, params);
		if (PropertyUtil.objectNotEmpty(userAnswerCount.get(0)) && Integer.parseInt(userAnswerCount.get(0).toString()) > 0) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * 配置问卷列表
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Xupp
	 * @createDate 2014-8-27 LEFT QuestionnaireSurveyObject qs
	 */
	@Override
	public List<QuestionnaireQueryBean> configureQuestionnaire(QuestionnaireQueryCommond commond) throws Exception {
		List<QuestionnaireQueryBean> findList = new ArrayList<QuestionnaireQueryBean>();
		// if(PropertyUtil.objectNotEmpty(commond.getSearchClassId()) ){
		StringBuffer hql = new StringBuffer();

		Map<String, Object> params = new HashMap<String, Object>();
		hql.append(" select q.questionnaireId as questionnaireId ,q.questionnaireName as questionnaireName" + ",q.surveyObjectType as surveyObjectType ,q.beginDate as beginDate"
				+ ",q.endDate as endDate,q.surveyType as surveyType" + ",q.questionnaireExplain as questionnaireExplain,q.publishState as publishState,q.activeState as activeState"
				+ ",q.createDate as createDate from QuestionnaireEntity q   where q.activeState = :activeState " + " AND  q.publishState = :publishState  ");

		// 已选列表
		if (commond.getSearchType() != null && commond.getSearchType().equals(QuestionnaireQueryCommond.SELECTED)) {
			hql.append("  and  q.questionnaireId in (select qs.questionnaire.questionnaireId from  QuestionnaireSurveyObject qs  where  qs.surveyObjectId =:surveyObjectId )  ");
		} else if (commond.getSearchType() != null && commond.getSearchType().equals(QuestionnaireQueryCommond.NONSSELECTED)) {
			// 待选列表
			hql.append("  and  q.questionnaireId not in (select qs.questionnaire.questionnaireId from  QuestionnaireSurveyObject qs  where qs.surveyObjectId =:surveyObjectId )");
		}
		// 班级ID
		params.put("surveyObjectId", commond.getSurveyObjectId());

		// 问卷名称
		if (commond.getSearchQuestionnaireName() != null && !"".equals(commond.getSearchQuestionnaireName())) {
			hql.append(" and q.questionnaireName like :questionnaireName ");
			params.put("questionnaireName", "%" + commond.getSearchQuestionnaireName() + "%");
		}
		// 创建单位
		if (commond.getSearchCreateUnit() != null && !"".equals(commond.getSearchCreateUnit())) {
			hql.append(" and q.department.deptName like :createUnit");
			params.put("createUnit", "%" + commond.getSearchCreateUnit() + "%");
		}
		// 结束时间
		if (commond.getSearchEndTime() != null && !"".equals(commond.getSearchEndTime())) {
			hql.append(" and q.endDate <= :endDate  ");
			params.put("endDate", commond.getSearchEndTime());
		}
		// 开始时间
		if (commond.getSearchStartTime() != null && !"".equals(commond.getSearchStartTime())) {
			hql.append(" and q.beginDate >= :beginDate ");
			params.put("beginDate", commond.getSearchStartTime());
		}
		// 创建人
		if (commond.getSearchUserName() != null && !"".equals(commond.getSearchUserName())) {
			hql.append(" and q.user.userName like :userName ");
			params.put("userName", "%" + commond.getSearchUserName() + "%");
		}
		// 问卷类型
		if (commond.getSearchQuestionnaireType() != null && !"".equals(commond.getSearchQuestionnaireType())) {
			hql.append(" and q.questionnaireType.dataCode = :questionnaireType ");
			params.put("questionnaireType", commond.getSearchQuestionnaireType());
		}
		// 发布状态
		if (commond.getSearchPublishState() != null && !"".equals(commond.getSearchPublishState())) {
			hql.append(" and q.publishState = :publishState ");
			params.put("publishState", commond.getSearchPublishState());
		}
		if (commond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = commond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				hql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					hql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		}
		params.put("activeState", QuestionnaireQueryCommond.IS_ENABLE);
		params.put("publishState", QuestionnaireQueryCommond.IS_PUBLISH);// 发布状态:发布
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(QuestionnaireQueryBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);

		// 查询总数量
		Long count = this.count(queryCondition);
		int firstResult = commond.calculate(count.longValue());

		findList = (List<QuestionnaireQueryBean>) this.findList(queryCondition, firstResult, commond.getLimit());
		// }
		return findList;
	}

	/**
	 * 配置课程问卷列表
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Xupp
	 * @createDate 2014-8-27 LEFT QuestionnaireSurveyObject qs
	 */
	@Override
	public List<QuestionnaireQueryBean> configureCourseQuestionnaire(QuestionnaireQueryCommond commond) throws Exception {
		List<QuestionnaireQueryBean> findList = new ArrayList<QuestionnaireQueryBean>();
		if (PropertyUtil.objectNotEmpty(commond.getSearchClassId())) {
			StringBuffer hql = new StringBuffer();

			Map<String, Object> params = new HashMap<String, Object>();
			hql.append(" select q.questionnaireId as questionnaireId ,q.questionnaireName as questionnaireName" + ",q.surveyObjectType as surveyObjectType ,q.beginDate as beginDate"
					+ ",q.endDate as endDate,q.surveyType as surveyType" + ",q.questionnaireExplain as questionnaireExplain,q.publishState as publishState,q.activeState as activeState"
					+ ",q.createDate as createDate from QuestionnaireEntity q   where q.activeState = :activeState " + " AND  q.publishState = :publishState  ");

			// 已选列表
			if (commond.getSearchType() != null && commond.getSearchType().equals(QuestionnaireQueryCommond.SELECTED)) {
				hql.append("  and  q.questionnaireId in (select qs.questionnaire.questionnaireId from  QuestionnaireSurveyObject qs  where  qs.surveyObjectId in (:courseids) )  ");
			} else {
				// 待选列表
				hql.append("  and  q.questionnaireId not in (select qs.questionnaire.questionnaireId from  QuestionnaireSurveyObject qs  where qs.surveyObjectId in (:courseids) )");
			}
			// 课程ID
			params.put("courseids", commond.getCourseIds());

			// 问卷名称
			if (commond.getSearchQuestionnaireName() != null && !"".equals(commond.getSearchQuestionnaireName())) {
				hql.append(" and q.questionnaireName like :questionnaireName ");
				params.put("questionnaireName", "%" + commond.getSearchQuestionnaireName() + "%");
			}
			// 创建单位
			if (commond.getSearchCreateUnit() != null && !"".equals(commond.getSearchCreateUnit())) {
				hql.append(" and q.department.deptName like :createUnit");
				params.put("createUnit", "%" + commond.getSearchCreateUnit() + "%");
			}
			// 结束时间
			if (commond.getSearchEndTime() != null && !"".equals(commond.getSearchEndTime())) {
				hql.append(" and q.endDate <= :endDate  ");
				params.put("endDate", commond.getSearchEndTime());
			}
			// 开始时间
			if (commond.getSearchStartTime() != null && !"".equals(commond.getSearchStartTime())) {
				hql.append(" and q.beginDate >= :beginDate ");
				params.put("beginDate", commond.getSearchStartTime());
			}
			// 创建人
			if (commond.getSearchUserName() != null && !"".equals(commond.getSearchUserName())) {
				hql.append(" and q.user.userName like :userName ");
				params.put("userName", "%" + commond.getSearchUserName() + "%");
			}
			// 问卷类型
			if (commond.getSearchQuestionnaireType() != null && !"".equals(commond.getSearchQuestionnaireType())) {
				hql.append(" and q.questionnaireType.dataCode = :questionnaireType ");
				params.put("questionnaireType", commond.getSearchQuestionnaireType());
			}
			// 发布状态
			if (commond.getSearchPublishState() != null && !"".equals(commond.getSearchPublishState())) {
				hql.append(" and q.publishState = :publishState ");
				params.put("publishState", commond.getSearchPublishState());
			}
			if (commond.getSortInfos() != null) {
				SortInfoBean sortInfoBean = commond.getSortInfos();
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
					hql.append(" order by " + sortInfoBean.getProperty().trim());
					if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
						hql.append(" " + sortInfoBean.getDirection().trim());
					}
				}
			}
			params.put("activeState", QuestionnaireQueryCommond.IS_ENABLE);
			params.put("publishState", QuestionnaireQueryCommond.IS_PUBLISH);// 发布状态:发布
			IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
			defaultQueryConditionBuilder.setResultObject(QuestionnaireQueryBean.class);
			defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
			IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
			queryCondition.setParameters(params);

			// 查询总数量
			Long count = this.count(queryCondition);
			int firstResult = commond.calculate(count.longValue());

			findList = (List<QuestionnaireQueryBean>) this.findList(queryCondition, firstResult, commond.getLimit());
		}
		return findList;
	}

	/**
	 * @查询评估开始和结束时间
	 * @Name: assessTime
	 * @Description: @param queryCommond
	 * @Description: @param request
	 * @Description: @param response
	 * @Description: @param resultState
	 * @Description: @return
	 * @Description: @throws Exception
	 * @Author: GUOLEI
	 * @Version: V1.00
	 * @Create Date: 2016-2-5下午2:19:45
	 * @Parameters: QuestionnaireController
	 * @Return: String
	 */
	@Override
	public QuestionnaireQueryBean assessCourseTime(QuestionnaireValidCommond validcommond) throws Exception {
		QuestionnaireQueryBean questionnaireQuery = null;
		if (PropertyUtil.objectNotEmpty(validcommond.getCourseIds()) && PropertyUtil.objectNotEmpty(validcommond.getClassId())) {
			StringBuffer sql = new StringBuffer();

			Map<String, Object> params = new HashMap<String, Object>();

			// 如果只有一个课程的话就取它自己的课程评估时间，如果是多个课程的话取它所属班级的开结班时间作为默认值。
			if (validcommond.getCourseIds().length == 1) {
				sql.append("select str_to_date(case when tcc.course_assess_start_time is null then tc.end_date-1 else tcc.course_assess_start_time end,'%Y%m%d') as \"assessCourseStartDay\","
						+ "str_to_date(case when tcc.course_assess_end_time is null then tc.end_date+2 else tcc.course_assess_end_time end,'%Y%m%d') as \"assessCourseEndDay\"   from training_class_course tcc "
						+ "left join training_class tc on tcc.class_id=tc.class_id where tcc.class_course_id=:courseid");
				params.put("courseid", validcommond.getCourseIds()[0]);
			} else {
				sql.append(" select tc.end_date-1  as \"assessCourseStartDay\"    , " + "tc.end_date+2 as \"assessCourseEndDay\"  " + "from  training_class tc  where tc.class_id  =:classId ");
				// 班级ID
				params.put("classId", validcommond.getClassId());
			}

			IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
			defaultQueryConditionBuilder.setResultObject(QuestionnaireQueryBean.class);
			defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
			defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
			IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
			queryCondition.setParameters(params);
			List<QuestionnaireQueryBean> resultsList = new ArrayList<QuestionnaireQueryBean>();
			resultsList = (List<QuestionnaireQueryBean>) this.findList(queryCondition);

			if (PropertyUtil.objectNotEmpty(resultsList) && resultsList.size() > 0 && PropertyUtil.objectNotEmpty(resultsList.get(0))) {
				questionnaireQuery = resultsList.get(0);
			}
		}

		return questionnaireQuery;
	}

	/**
	 * @更新评估开始时间和评估结束时间
	 * @Name: updateAssessTime
	 * @Description: @param validcommond
	 * @Description: @param request
	 * @Description: @param response
	 * @Description: @param resultState
	 * @Description: @return
	 * @Description: @throws Exception
	 * @Author: GUOLEI
	 * @Version: V1.00
	 * @Create Date: 2016-2-5下午2:53:27
	 * @Parameters: QuestionnaireController
	 * @Return: String
	 */
	public String updateAssessTime(QuestionnaireValidCommond validcommond) {
		if (PropertyUtil.objectNotEmpty(validcommond.getClassId())) {
			StringBuffer sql = new StringBuffer();

			SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
			try {
				if (PropertyUtil.objectNotEmpty(validcommond.getClassId())) {
					if (PropertyUtil.objectNotEmpty(validcommond.getAssessStartDay()) && PropertyUtil.objectNotEmpty(validcommond.getAssessEndDay())) {
						Map<String, Object> params = new HashMap<String, Object>();
						sql.append(" update training_class set " + "ASSESS_START_TIME= :assessStartDay  ," + "ASSESS_END_TIME =  :assessEndDay,    "
								+ " LATEST_OPERATE_DATE = :lastOperateDate where class_id = :classId ");

						params.put("classId", validcommond.getClassId());
						params.put("assessStartDay", simpleDateFormat.parse(validcommond.getAssessStartDay()));
						params.put("assessEndDay", simpleDateFormat.parse(validcommond.getAssessEndDay()));
						params.put("lastOperateDate", new Date());
						this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
				return "FAILURE";
			}
		}
		return "SUCESS";

	}

	/**
	 * @更新课程评估开始时间和评估结束时间
	 * @Name: updateAssessTime
	 * @Description: @param validcommond
	 * @Description: @param request
	 * @Description: @param response
	 * @Description: @param resultState
	 * @Description: @return
	 * @Description: @throws Exception
	 * @Author: GUOLEI
	 * @Version: V1.00
	 * @Create Date: 2016-2-5下午2:53:27
	 * @Parameters: QuestionnaireController
	 * @Return: String
	 */
	public String updateCourseAssessTime(QuestionnaireValidCommond validcommond) {
		if (PropertyUtil.objectNotEmpty(validcommond.getCourseIds())) {
			StringBuffer sql = new StringBuffer();

			SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
			try {
				if (PropertyUtil.objectNotEmpty(validcommond.getAssessCourseStartDay()) && PropertyUtil.objectNotEmpty(validcommond.getAssessCourseEndDay())) {
					Map<String, Object> params = new HashMap<String, Object>();
					sql.append(" update training_class_course set " + "COURSE_ASSESS_START_TIME= :assessStartDay  ," + "COURSE_ASSESS_END_TIME =  :assessEndDay    "
							+ "where class_course_id =:courseId ");
					for (String courseId : validcommond.getCourseIds()) {
						params.put("courseId", courseId);
						params.put("assessStartDay", simpleDateFormat.parse(validcommond.getAssessCourseStartDay()));
						params.put("assessEndDay", simpleDateFormat.parse(validcommond.getAssessCourseEndDay()));
						this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
				return "FAILURE";
			}
		}
		return "SUCESS";

	}

	/**
	 * @删除问卷
	 * @Name: updateAssessTime
	 * @Description: @param validcommond
	 * @Description: @param request
	 * @Description: @param response
	 * @Description: @param resultState
	 * @Description: @return
	 * @Description: @throws Exception
	 * @Author: GUOLEI
	 * @Version: V1.00
	 * @Create Date: 2016-2-5下午2:53:27
	 * @Parameters: QuestionnaireController
	 * @Return: String
	 */
	public String deleteQuestionnaires(QuestionnaireValidCommond validcommond) throws Exception {
		if (PropertyUtil.objectNotEmpty(validcommond.getClassId())) {
			StringBuffer sql = new StringBuffer();
			try {
				if (PropertyUtil.objectNotEmpty(validcommond.getClassId())) {
					Map<String, Object> params = new HashMap<String, Object>();
					sql.append("delete    from   QUESTIONNAIRE_SURVEY_OBJECT   where  survey_object_id=:classId   and   questionnaire_id in (:questionnaireIds) ");
					params.put("classId", validcommond.getClassId());
					params.put("questionnaireIds", validcommond.getQuestionnaireIds());
					deleteClassQuestionnaireAnswer(validcommond);
					this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
				}
			} catch (Exception e) {
				e.printStackTrace();
				return "FAILURE";
			}
		}
		return "SUCESS";
	}

	// 删除班级问卷相关答案
	public void deleteClassQuestionnaireAnswer(QuestionnaireValidCommond validcommond) {
		String sql = "delete from QUESTIONNAIRE_USER_ANSWER where questionnaire_id=:questionnaireIds and class_id=:classId";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("classId", validcommond.getClassId());
		params.put("questionnaireIds", validcommond.getQuestionnaireIds());
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}

	/**
	 * @查询评估开始和结束时间
	 * @Name: assessTime
	 * @Description: @param queryCommond
	 * @Description: @param request
	 * @Description: @param response
	 * @Description: @param resultState
	 * @Description: @return
	 * @Description: @throws Exception
	 * @Author: GUOLEI
	 * @Version: V1.00
	 * @Create Date: 2016-2-5下午2:19:45
	 * @Parameters: QuestionnaireController
	 * @Return: String
	 */
	public QuestionnaireQueryBean assessTime(QuestionnaireValidCommond validcommond) throws Exception {
		QuestionnaireQueryBean questionnaireQuery = null;
		if (PropertyUtil.objectNotEmpty(validcommond.getClassId())) {
			StringBuffer sql = new StringBuffer();

			Map<String, Object> params = new HashMap<String, Object>();
			sql.append(" select  str_to_date(case tc.ASSESS_START_TIME when null then tc.end_date - 1 else tc.ASSESS_START_TIME end,'%Y-%m-%d') as \"assessStartDay\", "
					+ "str_to_date(case tc.ASSESS_END_TIME when null then tc.end_date + 2 else tc.ASSESS_END_TIME end,'%Y-%m-%d') as \"assessEndDay\"  "
					+ "from  training_class tc  where tc.class_id  =:classId ");
			// 班级ID
			params.put("classId", validcommond.getClassId());
			IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
			defaultQueryConditionBuilder.setResultObject(QuestionnaireQueryBean.class);
			defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
			defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
			IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
			queryCondition.setParameters(params);
			List<QuestionnaireQueryBean> resultsList = new ArrayList<QuestionnaireQueryBean>();
			resultsList = (List<QuestionnaireQueryBean>) this.findList(queryCondition);

			if (PropertyUtil.objectNotEmpty(resultsList) && resultsList.size() > 0 && PropertyUtil.objectNotEmpty(resultsList.get(0))) {
				questionnaireQuery = resultsList.get(0);
			}
		}

		return questionnaireQuery;
	}

	/**
	 * 问卷列表
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author GUOLEI
	 * @createDate 2014-8-27 LEFT QuestionnaireSurveyObject qs
	 */
	@Override
	public List<QuestionnaireQueryBean> queryUserQuestionnaireList(QuestionnaireQueryCommond commond) throws Exception {
		List<QuestionnaireQueryBean> findList = new ArrayList<QuestionnaireQueryBean>();
		if (PropertyUtil.objectNotEmpty(commond.getSearchClassId()) && PropertyUtil.objectNotEmpty(commond.getSearchUserId())) {
			StringBuffer sql = new StringBuffer();

			Map<String, Object> params = new HashMap<String, Object>();
			sql.append("select "
					+ "qq.questionnaireId as \"questionnaireId\" , "
					+ "qq.questionnaireName as \"questionnaireName\" , "
					+ "qq.surveyObjectType as \"surveyObjectType\",  "
					+ "qq.surveyType as \"surveyType\" ,  "
					+ "qq.questionnaireExplain as \"questionnaireExplain\", "
					+ "qq.publishState as \"publishState\", "
					+ "qq.activeState as \"activeState\" , "
					+ "qq.testCondition as \"testCondition\"     "
					+ "from  "
					+ "( "
					+ "select q.QUESTIONNAIRE_ID as questionnaireId  "
					+ ",q.QUESTIONNAIRE_NAME as questionnaireName  "
					+ ",q.SURVEY_OBJECT_TYPE as surveyObjectType  "
					+ ",q.SURVEY_TYPE as surveyType   "
					+ ",q.QUESTIONNAIRE_EXPLAIN as questionnaireExplain "
					+ ",q.PUBLISH_STATE as publishState "
					+ ",q.ACTIVE_STATE as activeState  "
					+ ",1 as testCondition  "
					+ "from QUESTIONNAIRE q  where  q.questionnaire_id  not in  (select qua.questionnaire_id  from  QUESTIONNAIRE_USER_ANSWER qua where qua.userid = :userId and qua.class_id=:classId ) "
					+ "union " + "select q.QUESTIONNAIRE_ID as questionnaireId  " + ",q.QUESTIONNAIRE_NAME as questionnaireName  " + ",q.SURVEY_OBJECT_TYPE as surveyObjectType  "
					+ ",q.SURVEY_TYPE as surveyType   " + ",q.QUESTIONNAIRE_EXPLAIN as questionnaireExplain " + ",q.PUBLISH_STATE as publishState " + ",q.ACTIVE_STATE as activeState  "
					+ ",2 as testCondition  "
					+ "from QUESTIONNAIRE q where   q.questionnaire_id  in  (select qua.questionnaire_id  from  QUESTIONNAIRE_USER_ANSWER qua where qua.userid = :userId  and qua.class_id=:classId ) "
					+ ") qq " +

					"where qq.activeState = :activeState     " + "AND  qq.publishState =:publishState  "
					+ "and  qq.questionnaireId in (select qs.QUESTIONNAIRE_ID from  QUESTIONNAIRE_SURVEY_OBJECT qs  where  qs.SURVEY_OBJECT_ID = :classId ) ");

			// 班级ID
			params.put("classId", commond.getSearchClassId());
			// 人员ID
			params.put("userId", commond.getSearchUserId());

			params.put("activeState", QuestionnaireQueryCommond.IS_ENABLE);
			params.put("publishState", QuestionnaireQueryCommond.IS_PUBLISH);// 发布状态:发布
			IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
			defaultQueryConditionBuilder.setResultObject(QuestionnaireQueryBean.class);
			defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
			defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
			IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
			queryCondition.setParameters(params);

			findList = (List<QuestionnaireQueryBean>) this.findList(queryCondition);
		}
		return findList;
	}

	/**
	 * 查询课程信息
	 * 
	 * @author RongSL
	 * @Date 2016-05-25
	 * */
	@Override
	public QuestionnaireValidCommond findCourseInfo(QuestionnaireValidCommond commond) throws Exception {
		String sql = "select tcc.CLASS_COURSE_ID \"courseId\", tcc.course_name \"courseName\",  tcc.speaker \"speaker\", "
				+ "tc.class_name \"className\", bb.data_name \"speakWay\", tc.training_dept_name \"ministrieName\", "
				+ "ifnull(tcc.course_assess_start_time,tc.end_date-1) \"assessCourseStartDay\", "
				+ "ifnull(tcc.course_assess_end_time,tc.end_date+2) \"assessCourseEndDay\", "
				+ "ifnull(tc.QUESTIONNAIRE_METHOD,1) \"questionnaireMethod\" from training_class_course tcc left join training_class tc on tc.class_id = tcc.class_id " 
				+ "left join base_basedata bb on tcc.speakway=bb.data_code "
				+ "where tcc.class_course_id =:courseid ";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("courseid", commond.getClassId());
		Object[] object = (Object[]) this.executeFind(QueryStringType.SQL, sql, params).get(0);
		commond = new QuestionnaireValidCommond();
		commond.setCourseId((String) object[0]);
		commond.setCourseName((String) object[1]);
		commond.setSpeaker((String) object[2]);
		commond.setClassName((String) object[3]);
		commond.setSpeakWay((String) object[4]);
		commond.setMinistrieName((String) object[5]);
		commond.setAssessCourseStartDay((String) object[6]);
		commond.setAssessCourseEndDay((String) object[7]);
		commond.setQuestionnaireMethod(Integer.valueOf(object[8].toString()));
		return commond;
	}

	/**
	 * @author RongSL
	 * @Date 2016-05-26 根据课程id找到相关班级id
	 * */
	@Override
	public String findClassId(QuestionnaireQueryCommond commond) throws Exception {
		String sql = "select tc.class_id from training_class tc left join training_class_course tcc on tc.class_id=tcc.class_id  where tc.class_id=:id or tcc.class_course_id=:id ";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", commond.getSearchClassId());
		return (String) this.executeFind(QueryStringType.SQL, sql, params).get(0);
	}

	/**
	 * 查询课程单选题汇总数据
	 * @Date 2016-09-27
	 * @author RongSL
	 * */
	@Override
	public List<ClassQuestionnaireSummary> findClassCourseQuestionSummary(QuestionnaireQueryCommond commond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer(" select "
				+ "q.questionnaire_name \"questionnaireName\","
				+ "ifnull(tc.questionnaire_method,1) \"questionnaireMethod\","
				+ "tc.class_id \"classId\", tc.class_name \"className\"," +
				"tcc.class_course_id \"classCourseId\", "
				+ "qb.question_name \"questionName\","
				+ "qb.question_type \"questionType\", "
				+ "tcc.course_name \"courseName\","
				+ " tcc.speaker \"speaker\","
				+ "format(avg(qbo.option_name),2) \"average\", "
				+ "CONVERT(ifnull(oA.allAverage,0.0) ,DECIMAL) \"allAverage\","
				+" case concat(format(count(qua.userid)* 100 / cou.userCount,2 ) * 100,'%')  when '%' then '-' else concat(format(count(qua.userid)* 100 / cou.userCount,2),'%') end \"recoveryRate\" "
				+ "from QUESTIONNAIRE_SURVEY_OBJECT qso inner join questionnaire q on qso.questionnaire_id = q.questionnaire_id "
				+ "inner join training_class_course tcc  on qso.survey_object_id=tcc.class_course_id inner join training_class tc on tcc.class_id = tc.class_id  "
				+ "inner join QUESTIONNAIRE_QUESTION qq on qq.questionnaire_id = q.questionnaire_id  inner join question_bank qb on qb.question_id = qq.question_id "
				+ "inner join QUESTIONNAIRE_USER_ANSWER qua on qb.question_id = qua.question_id and qua.class_id = tcc.class_course_id "
				+ "inner join question_bank_option qbo on qbo.option_num = qua.question_option "
				+ "and qbo.question_id = qua.question_id left join ( select count(tcu.user_id) userCount,tcu.class_id from training_class_user tcu "
				+ "where tcu.auditing_state in (1,5,6) and tcu.active_state=1  group by tcu.class_id ) cou on cou.class_id=tc.class_id " +
				"inner join (select format(avg(qbo.option_name),2) allAverage, qua.class_id  from questionnaire_user_answer qua  " +
				"inner join question_bank qb on qb.question_id = qua.question_id " +
				"inner join question_bank_option qbo on qbo.option_num = qua.question_option and qbo.question_id = qua.question_id " +
				"where qb.question_type = 'danxt' and qb.is_score_type = 1 group by qua.class_id) oA on oA.class_id = tcc.class_course_id where 1=1 ");
		if (PropertyUtil.objectNotEmpty(commond.getSearchClassIds())) {
			sql.append(" and tc.class_id in (:classIds) ");
			params.put("classIds", commond.getSearchClassIds());
		}
		sql.append("and qb.question_type='danxt' and qb.is_score_type=1 and q.publish_state=1 and qua.userid in (select b.user_id from questionnaire_user_answer qua " +
				"inner join training_class_course tcc on tcc.class_course_id = qua.class_id  inner join training_class_user b on qua.userid = b.user_id and qua.class_id=b.class_id " +
				"where tcc.class_id =tc.class_id  and b.auditing_state in (1, 5,6) group by b.user_id) group by   q.questionnaire_name,tc.questionnaire_method, "
				+ "tc.class_name,tcc.class_course_id,oA.allAverage,tc.class_id, qb.question_name,qb.question_type, tcc.course_name, tcc.speaker,cou.userCount, "
				+ "qq.order_num,tcc.create_date  order by tcc.create_date,qq.order_num  ");
		List<ClassQuestionnaireSummary> list = (List<ClassQuestionnaireSummary>) this.executeFind(QueryStringType.SQL, sql.toString(), params, ClassQuestionnaireSummary.class);
		return list;	
	}
	
	/**
	 * 查询课程问答题汇总数据
	 * @Date 2016-09-27
	 * @author RongSL
	 * */
	@Override
	public List<ClassQuestionnaireSummary> findClassCourseQuestionSummaryRemarks(QuestionnaireQueryCommond commond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer(" select distinct "
				+ "concat(tcu.user_name,':',qua.question_answer) \"questionAnswerText\","
				+ "qb.question_name \"questionAnswer\"," 
				+"tcc.course_name \"courseName\",qb.question_id \"questionId\",tc.class_id \"classId\",tcu.user_id \"userId\", tcc.class_course_id \"classCourseId\",tcc.create_date \"createDate\" " +
				"from QUESTIONNAIRE_SURVEY_OBJECT qso  inner join questionnaire q on qso.questionnaire_id = q.questionnaire_id  " +
				"inner join training_class_course tcc  on qso.survey_object_id=tcc.class_course_id " +
				"inner join training_class tc  on tcc.class_id = tc.class_id " +
				" inner join QUESTIONNAIRE_QUESTION qq on qq.questionnaire_id = q.questionnaire_id " +
				" inner join question_bank qb    on qb.question_id = qq.question_id " +
				" inner join QUESTIONNAIRE_USER_ANSWER qua   on qb.question_id = qua.question_id and qua.class_id = tcc.class_course_id  " +
				"inner join training_class_user tcu on tcu.user_id = qua.userid  where qb.question_type='wendt'  ");
		if (PropertyUtil.objectNotEmpty(commond.getSearchClassIds())) {
			sql.append(" and tc.class_id in (:classIds) ");
			params.put("classIds", commond.getSearchClassIds());
		}
		sql.append("   order by tcc.create_date  ");
		List<ClassQuestionnaireSummary> list = (List<ClassQuestionnaireSummary>) this.executeFind(QueryStringType.SQL, sql.toString(), params, ClassQuestionnaireSummary.class);
		return list;
	}
	
	/**
	 * 查询培训班汇总数据
	 * @Date 2016-10-08
	 * @author RongSL
	 * */
	@Override
	public List<ClassQuestionnaireSummary> findClassQuestionSummaryRemarks(QuestionnaireQueryCommond commond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer(" SELECT t.questionnaireName \"questionnaireName\",t.startDate \"startDate\",t.sponsorName \"sponsorName\","
				+ "t.className \"className\",ifnull(t.questionnaireMethod, 1) \"questionnaireMethod\",ifnull(t.userCount,0) \"userCount\",ifnull(t.assCount,0) \"assCount\","
//				+ "sum(t.count) \"count\","
				+ "round(t.allAverage, 2) \"allAverage\",case t.assCountRate when '%' then '-' else t.assCountRate end \"assCountRate\","
				+ "group_concat(t.questionName order by t.orderNum) \"questionName\","
//				+ "-- group_concat(t.questionsProblemSets order by t.orderNum) \"questionsProblemSets\","
				+ "group_concat(t.average order by t.orderNum) \"averageStr\" "
				+ "FROM(SELECT q.questionnaire_name questionnaireName,oC.allAverage,tc.class_id classId,"
				+ "concat(date_format(tc.create_date,'%Y'),'年',DATE_FORMAT(tc.create_date,'%c'),'月') startDate,tc.sponsor_Name sponsorName,"
				+ "tc.questionnaire_method questionnaireMethod,tc.class_name className,qb.question_name questionName,"
				+ "qb.questions_problem_sets questionsProblemSets,ifnull (uc.userC, 0) userCount,ifnull(a.userD, 0) assCount,"
				+ "ROUND(avg(qbo.option_name), 2) average,oC.count count,ROUND(a.userD / uc.userC, 2) * 100 || '%' assCountRate,"
				+ "qq.order_num orderNum FROM questionnaire_survey_object qso LEFT JOIN questionnaire q ON qso.questionnaire_id = q.questionnaire_id "
				+ "LEFT JOIN training_class tc ON qso.survey_object_id = tc.class_id LEFT JOIN questionnaire_question qq ON qq.questionnaire_id = q.questionnaire_id "
				+ "LEFT JOIN question_bank qb ON qb.question_id = qq.question_id LEFT JOIN questionnaire_user_answer qua ON qb.question_id = qua.question_id "
				+ "AND qua.class_id = tc.class_id LEFT JOIN question_bank_option qbo ON qbo.option_num = qua.question_option "
				+ "AND qbo.question_id = qua.question_id LEFT JOIN training_class_user tcu ON qua.userid = tcu.user_id AND tcu.class_id = tc.class_id "
				+ "LEFT JOIN (SELECT count(1) userC, tcu.class_id FROM training_class_user tcu WHERE tcu.auditing_state IN (1, 5) GROUP BY tcu.class_id) uc ON uc.class_id = tc.class_id "
				+ "LEFT JOIN (SELECT count(1) userD,a.class_id FROM (SELECT tcu.user_id,tc.class_id FROM questionnaire_user_answer qua LEFT JOIN training_class tc ON tc.class_id = qua.class_id "
				+ "LEFT JOIN training_class_user tcu ON qua.userid = tcu.user_id WHERE tcu.auditing_state IN (1, 5, 6) GROUP BY tcu.user_id,tc.class_id) a GROUP BY a.class_id) a "
				+ "ON a.class_id = tc.class_id LEFT JOIN (SELECT sum(qbo.option_name) count,avg(qbo.option_name) allAverage,qua.class_id FROM questionnaire_user_answer qua "
				+ "LEFT JOIN question_bank qb ON qb.question_id = qua.question_id LEFT JOIN question_bank_option qbo ON qbo.option_num = qua.question_option "
				+ "AND qbo.question_id = qua.question_id WHERE qb.question_type = 'danxt' AND qb.is_score_type = 1 GROUP BY qua.class_id) oC ON oC.class_id = tc.class_id "
				+ "WHERE 1 = 1 AND tcu.auditing_state IN (1, 5, 6) ");
				if (PropertyUtil.objectNotEmpty(commond.getSearchClassIds())&&PropertyUtil.objectNotEmpty(commond.getSearchClassIds()[0])) {
					sql.append(" and tc.class_id in (:classIds) ");
					params.put("classIds", commond.getSearchClassIds());
				}else{
					sql.append(" and tc.class_id in (:classIds) ");
					params.put("classIds", findQuestionnaireClassId());
				}
				sql.append("and qb.question_type = 'danxt' and qb.is_score_type = 1 and q.publish_state = 1 and tc.active_state = 1 " +
//						"--and qso.questionnaire_id='12122' " +	//扩展，以后可能会导出指定问卷
						"and qua.userid in (select tcu.user_id  from questionnaire_user_answer qua left join training_class tc on tc.class_id = qua.class_id " +
						"left join training_class_user tcu on qua.userid = tcu.user_id where tc.class_id =qso.survey_object_id and  tcu.auditing_state in (1, 5,6) group by tcu.user_id) " +
						"group by q.questionnaire_name, tc.class_id, tc.questionnaire_method, tc.class_name, qb.question_name, qb.questions_problem_sets, tc.sponsor_Name, tc.create_date, " +
						"uc.userC, a.userD, oC.count,oC.allAverage,  qq.order_num order by tc.create_date, qq.order_num) t  order by t.startDate");
		List<ClassQuestionnaireSummary> list = (List<ClassQuestionnaireSummary>) this.executeFind(QueryStringType.SQL, sql.toString(), params, ClassQuestionnaireSummary.class);
		return list;
	}
	
	/**
	 * 获得所有配置问卷的培训班编号
	 * @author RongSL
	 * @createDate 2016-10-08
	 * */
	public String[] findQuestionnaireClassId(){
		String sql=" select tc.class_id FROM training_class tc  " +
				"left join QUESTIONNAIRE_SURVEY_OBJECT qso on qso.Survey_Object_Id = tc.class_id " +
				"left join questionnaire q on qso.questionnaire_id = q.questionnaire_id " +
				"where q.publish_state=1 and tc.active_state=1";
		List<String> l=(List<String>) this.executeFind(QueryStringType.SQL, sql, null);
		if(PropertyUtil.objectNotEmpty(l)&&PropertyUtil.objectNotEmpty(l.get(0))){
			String [] id=new String[l.size()];
			for(int i=0;i<l.size();i++){
				id[i]=l.get(i);
			}
			System.out.println(l.size());
			return id;
		}
		return null;
	}
}
